package com.Dao;

import com.Dao.Info.Student;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

import static com.Dao.MYconnection.close;

/**
 * @Author:xuyuanhong
 * @Date:Created in 2022/12/16 15:05
 */
public class StudentDao {
    public static final String Driver_Name = "com.mysql.cj.jdbc.Driver";

    public static final String url = "jdbc:mysql://localhost:3306/school";
    public static final String User_name = "root";
    public static final String Password = "mmm18623908829";

    private static Connection connection = null;
    private static Statement stat = null;
    private static ResultSet rs = null;
    public static void init() throws ClassNotFoundException {
        try{
//            注册驱动
            Class.forName(Driver_Name);
//            连接mysql
            connection= DriverManager.getConnection(url,User_name,Password);
//             建立连接
//            stmt = connection.createStatement();
        }catch (Exception e){
            e.printStackTrace();
        }
    }


    public Student checkAccount(String id, String name) throws Exception {
//        List<Student> StudentList = new ArrayList<Student>();
        init();
//        stat = connection.createStatement();
        String sql = "select * from student where id = '" + id + "' and name = '" + name + "'";
        PreparedStatement preparedStatement = (PreparedStatement)connection.prepareStatement(sql);
//        rs = stat.executeQuery(sql);
        ResultSet rs = preparedStatement.executeQuery(sql);
        Student student= null;
        if(rs.next()){

            student = new Student();
            student.setID(rs.getString("id"));
            student.setName(rs.getString("name"));
            student.setSex(rs.getString("sex"));
            student.setDate(rs.getString("indate"));
            student.setNum(rs.getString("telnum"));
            student.setStatus(rs.getString("status"));
            student.setTerms(rs.getString("semester"));

            System.out.println("查询成功");

        }
        close(stat,connection);
        return student;
    }



    public List<Student> query() throws Exception {
        List<Student> StudentList = new ArrayList<Student>();
        init();
//        String sqlt = "set hive.exec.mode.local.auto=true";
//            String sql = "SELECT NAME ,COUNT(1) AS num\n" +
//                    "FROM kq \n" +
//                    "GROUP BY NAME";
        String sql = "select Sex,count(*)  as num from `205817` group by Sex";
//        PreparedStatement preparedStatement1 = (PreparedStatement)connection.prepareStatement(sqlt);
////        rs = stat.executeQuery(sql);
//        ResultSet rs1 = preparedStatement1.executeQuery(sqlt);
//            String sql = "select sex,count(1) from student group by sex;";
        PreparedStatement preparedStatement = (PreparedStatement)connection.prepareStatement(sql);
//        rs = stat.executeQuery(sql);
        ResultSet rs = preparedStatement.executeQuery(sql);
            while (rs.next()){
                Student u = null;
                u = new Student();
                u.setSex(rs.getString("Sex"));
                u.setNum(rs.getString("num"));

                System.out.println("查询成功");
                StudentList.add(u);

            }


        close(stat,connection);
        return StudentList;
    }

    public List<Student> queryone() throws Exception {
        List<Student> StudentList = new ArrayList<Student>();
        init();
//        String sqlt = "set hive.exec.mode.local.auto=true";
//            String sql = "SELECT NAME ,COUNT(1) AS num\n" +
//                    "FROM kq \n" +
//                    "GROUP BY NAME";
        String sql = "select semester,count(*)  as num from `205817` group by semester";
//        PreparedStatement preparedStatement1 = (PreparedStatement)connection.prepareStatement(sqlt);
////        rs = stat.executeQuery(sql);
//        ResultSet rs1 = preparedStatement1.executeQuery(sqlt);
//            String sql = "select sex,count(1) from student group by sex;";
        PreparedStatement preparedStatement = (PreparedStatement)connection.prepareStatement(sql);
//        rs = stat.executeQuery(sql);
        ResultSet rs = preparedStatement.executeQuery(sql);
        while (rs.next()){
            Student u = null;
            u = new Student();
            u.setTerms(rs.getString("semester"));
            u.setNum(rs.getString("num"));

            System.out.println("查询成功");
            StudentList.add(u);

        }


        close(stat,connection);
        return StudentList;
    }


    public List<Student> querytwo() throws Exception {
        List<Student> StudentList = new ArrayList<Student>();
        init();
//            String sql = "SELECT NAME ,COUNT(1) AS num\n" +
//                    "FROM kq \n" +
//                    "GROUP BY NAME";
        String sql = "SELECT Term ,COUNT(*) AS num\n" +
                "FROM `205817` \n" +
                "GROUP BY Term";
//            String sql = "select sex,count(1) from student group by sex;";
        PreparedStatement preparedStatement = (PreparedStatement)connection.prepareStatement(sql);
//        rs = stat.executeQuery(sql);
        ResultSet rs = preparedStatement.executeQuery(sql);
        while (rs.next()){
            Student u = null;
            u = new Student();
            u.setTerms(rs.getString("score"));
            u.setNum(rs.getString("num"));

            System.out.println("查询成功");
            StudentList.add(u);

        }


        close(stat,connection);
        return StudentList;
    }
}
